<!---
	Copyright 2010 Edward Trudeau, Jeremy Battle
    This software is freely available under the MIT License: http://www.opensource.org/licenses/mit-license.php
    http://ayc.googlecode.com
    
--->

<!--- permissions loader for ayc --->
<cftransaction>
    <cfquery name="qClearPerms" datasource="#request.config.dsn#">
    	Delete from aycUserObjectTypes;
        Delete from aycUserObjectPerms;
        Delete from aycPermObjectTypes;
        Delete from aycObjects;
        Delete from aycObjectTypes;
    </cfquery>
    
    <p>Permissions cleared</p>
    
    <cfquery name="qInsOT" datasource="#request.config.dsn#">
    	INSERT INTO aycObjectTypes (ObjectTypeName) Values ('NCSSS Field Database');
    </cfquery>
    
    <p>Object Type added</p>
    
    <cfquery name="qGetOT" datasource="#request.config.dsn#">
    	SELECT ObjectTypeId from aycObjectTypes
        WHERE ObjectTypeName = <cfqueryparam cfsqltype="cf_sql_varchar" value="NCSSS Field Database" />
    </cfquery>
    
    <cfset otid = qGetOT.ObjectTypeId />
    
    <p>OTID = <cfoutput>#otid#</cfoutput></p>
    
    <cfquery name="qGetObjs" datasource="#request.config.dsn#">
    	SELECT DISTINCT(ObjectName) FROM aycFieldCatalog
        ORDER BY ObjectName
    </cfquery>
    
    <cfoutput query="qGetObjs">
		<cfquery name="qInsObj" datasource="#request.config.dsn#">
            INSERT INTO aycObjects (ObjectTypeId, ObjectName)
            VALUES (<cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />,
            		<cfqueryparam cfsqltype="cf_sql_varchar" value="#qGetObjs.ObjectName#" />)
        </cfquery>
	</cfoutput>
    
    <p>Inserted <cfoutput>#qGetObjs.recordCount#</cfoutput> objects.</p>
    
    <cfquery datasource="#request.config.dsn#">
    	INSERT INTO aycPermObjectTypes
            (PermissionName, PermissionDesc, ObjectTypeId, Seq)
        VALUES
             ('Read Only','Can read, list, and report on objects', <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />, 1);
        INSERT INTO aycPermObjectTypes
            (PermissionName, PermissionDesc, ObjectTypeId, Seq)
        VALUES
             ('Create','Can create new objects', <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />, 2);
        INSERT INTO aycPermObjectTypes
            (PermissionName, PermissionDesc, ObjectTypeId, Seq)
        VALUES
             ('Modify','Can change existing objects', <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />, 3);
        INSERT INTO aycPermObjectTypes
            (PermissionName, PermissionDesc, ObjectTypeId, Seq)
        VALUES
             ('Delete','Can remove objects', <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />, 4);
        INSERT INTO aycPermObjectTypes
            (PermissionName, PermissionDesc, ObjectTypeId, Seq)
        VALUES
             ('Admin','Can add and remove permissions on objects', <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />, 5);
        INSERT INTO aycPermObjectTypes
            (PermissionName, PermissionDesc, ObjectTypeId, Seq)
        VALUES
             ('None','Denied access to all object functions', <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />, 0);
    </cfquery>
    
    <p>Created permissions for NCSSS object type</p>
    
    <cfquery name="qGetAdmin" datasource="#request.config.dsn#">
    	SELECT PermId FROM aycPermObjectTypes
        WHERE PermissionName = 'Admin'
        AND ObjectTypeId = <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />
    </cfquery>
    <cfset adminid = qGetAdmin.PermId />
    
    <p>Admin Permission = <cfoutput>#adminid#</cfoutput></p>
    
    <cfquery name="qGetObjIds" datasource="#request.config.dsn#">
    	SELECT ObjectId FROM aycObjects
        ORDER BY ObjectId
    </cfquery>
    
    <cfquery name="qGetET" datasource="#request.config.dsn#">
    	SELECT UserId from aycUsers
        WHERE Username = 'trudeaue'
    </cfquery>
    <cfset etid = qGetET.UserId />
    
    <cfquery name="qGetJB" datasource="#request.config.dsn#">
    	SELECT UserId from aycUsers
        WHERE Username = 'battlejj'
    </cfquery>
    <cfset jbid = qGetJB.UserId />
    
    <p>etid = <cfoutput>#etid#</cfoutput>; jbid = <cfoutput>#jbid#</cfoutput></p>
    
    <cfoutput query="qGetObjIds">
        <cfquery name="qInsETObjPerms" datasource="#request.config.dsn#">
            INSERT INTO aycUserObjectPerms
                (PermId, UserId, ObjectId)
            VALUES
                (<cfqueryparam cfsqltype="cf_sql_integer" value="#adminid#" />, <cfqueryparam cfsqltype="cf_sql_integer" value="#etid#" />, <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetObjIds.ObjectId#" />);
        </cfquery>
         <cfquery name="qInsJBObjPerms" datasource="#request.config.dsn#">
            INSERT INTO aycUserObjectPerms
                (PermId, UserId, ObjectId)
            VALUES
                (<cfqueryparam cfsqltype="cf_sql_integer" value="#adminid#" />, <cfqueryparam cfsqltype="cf_sql_integer" value="#jbid#" />, <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetObjIds.ObjectId#" />);
        </cfquery>
    </cfoutput>
    
    <p>Inserted <cfoutput>#qGetObjIds.recordCount#</cfoutput> permissions for et/jb</p>
    
    
    <cfquery name="qInsETObjTPerms" datasource="#request.config.dsn#">
        INSERT INTO aycUserObjectTypes
            (PermId, UserId, ObjectTypeId)
        VALUES
            (<cfqueryparam cfsqltype="cf_sql_integer" value="#adminid#" />, <cfqueryparam cfsqltype="cf_sql_integer" value="#etid#" />, <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />);
    </cfquery>
    <cfquery name="qInsJBObjTPerms" datasource="#request.config.dsn#">
        INSERT INTO aycUserObjectTypes
            (PermId, UserId, ObjectTypeId)
        VALUES
            (<cfqueryparam cfsqltype="cf_sql_integer" value="#adminid#" />, <cfqueryparam cfsqltype="cf_sql_integer" value="#jbid#" />, <cfqueryparam cfsqltype="cf_sql_integer" value="#otid#" />);
    </cfquery>
    
    <p>Set OT permissions for jb/et</p>
    
    <p>Done!</p>

</cftransaction>

